<?php

require_once('../lib/SqliteTriggerGenerator.php');
require_once('simpletest/autorun.php');

/**
 * Test that all sqlite-supported identifier delimiters are supported by 
 * parser and that resulting triggers run correctly.
 *
 *     Supported delimiters:
 *         * [table name]   (MS Access style)
 *         * "table name"   (double quotes)
 *         * 'table name'   (single quotes)
 *         * `table name`   (MySQL style)
 *         * table_name     (none)
 */
abstract class TestIdentifiersBase extends UnitTestCase {
    static $dsn = 'sqlite::memory:';
    protected $gen;
    protected $numExpTriggers = 0;

    public function setUp() {
        $this->gen = new SqliteTriggerGenerator(false, false);

        $this->conn = new PDO(TestIdentifiersBase::$dsn);
        $this->conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

        $this->prepareNames();
    }

    public function tearDown(){
        $this->conn = null;
    }

    /**
     * Prepare the names to be used as identifiers
     *
     * @param boolean $spaces   Use names with spaces
     * @access private
     */
    abstract function prepareNames($spaces = false);

    /**
     * Prepare the SQL statement
     *
     * @param string $before    The opening identifier (e.g. '[')
     * @param string $after     The closing identifier (e.g. ']')
     * @return string   SQL statement with identifiers replaced
     */
    abstract function prepareSQL($before = '', $after = '');

    public function assertNoPDOException($sql) {
        // Check for errors running test SQL
        try {
            $this->conn->exec($sql);
        } catch(PDOException $e) {
            $this->fail(sprintf("TEST ERROR: Create sql caused error: %s\nSQL: %s", $e->getMessage(), $sql));
        }

        $trigSQL = $this->gen->create($sql, 'Expected three trigger statements.');

        $this->assertEqual(count($trigSQL), $this->numExpTriggers);

        // Check for errors in generated trigger
        try {
            foreach($trigSQL as $tsql) {
                $out = $this->conn->exec($tsql);
            }
        } catch(PDOException $e) {
            $this->fail(sprintf("Error running trigger sql: %s\nTrigger SQL: %s", $e->getMessage(), $tsql));
        }
        
    }

    // Individual Tests

    public function testMySQLStyle() {
        $this->prepareNames(false);
        $createsql = $this->prepareSQL('`', '`');
        $this->assertNoPDOException($createsql);
    }

    public function testMySQLStyleWithSpaces() {
        $this->prepareNames(true);
        $createsql = $this->prepareSQL('`', '`');
        $this->assertNoPDOException($createsql);
    }

    public function testAccessStyle() {
        $this->prepareNames(false);
        $createsql = $this->prepareSQL('[', ']');
        $this->assertNoPDOException($createsql);
    }

    public function testAccessStyleWithSpaces() {
        $this->prepareNames(true);
        $createsql = $this->prepareSQL('[', ']');
        $this->assertNoPDOException($createsql);
    }

    public function testDoubleQuotes() {
        $this->prepareNames(false);
        $createsql = $this->prepareSQL('"', '"');
        $this->assertNoPDOException($createsql);
    }

    public function testDoubleQuotesWithSpaces() {
        $this->prepareNames(true);
        $createsql = $this->prepareSQL('"', '"');
        $this->assertNoPDOException($createsql);
    }

    public function testSingleQuotes() {
        $this->prepareNames(false);
        $createsql = $this->prepareSQL("'", "'");
        $this->assertNoPDOException($createsql);
    }

    public function testSingleQuotesWithSpaces() {
        $this->prepareNames(true);
        $createsql = $this->prepareSQL("'", "'");
        $this->assertNoPDOException($createsql);
    }

    public function testNone() {
        $this->prepareNames(false);
        $createsql = $this->prepareSQL('', '');
        $this->assertNoPDOException($createsql);
    }

    public function testNoneWithSpaces() {
        // TODO: assert an error or zero length array
    }
}


class IdentifierVariant1 extends TestIdentifiersBase {
    public function setUp() {
        parent::setUp();
        $this->numExpTriggers = 3;
    }

    public function prepareSQL($before = '', $after = '') {
        $sql = <<<EOT
CREATE TABLE {$before}{$this->table1}{$after} (
    {$before}{$this->IDcol}{$after} INTEGER NOT NULL PRIMARY KEY
);

CREATE TABLE {$before}{$this->table2}{$after} (
    {$before}{$this->IDcol}{$after} INTEGER NOT NULL PRIMARY KEY,
    {$before}{$this->refcol}{$after} INTEGER
          CONSTRAINT {$before}fk_foo_id{$after} REFERENCES {$before}{$this->table1}{$after}({$before}{$this->IDcol}{$after})
);
EOT;
        return $sql;
    }

    public function prepareNames($spaces = false) {
        if ($spaces == false) {
            $this->table1 = 'Foo';
            $this->table2 = 'Bar';
            $this->IDcol = 'ID';
            $this->refcol = 'FooID';
        } else {
            $this->table1 = 'Foo with spaces';
            $this->table2 = 'Bar with spaces';
            $this->IDcol = 'ID with spaces';
            $this->refcol = 'FooID with spaces';
        }
    }
}


class IdentifierVariant2 extends TestIdentifiersBase {
    public function setUp() {
        parent::setUp();
        $this->numExpTriggers = 6;
    }

    public function prepareSQL($before = '', $after = '') {
        $sql = <<<EOT
CREATE TABLE {$before}{$this->table1}{$after} (
    {$before}{$this->IDcol}{$after} INTEGER NOT NULL PRIMARY KEY,
    {$before}{$this->namecol}{$after} VARCHAR(16) NOT NULL
);

CREATE TABLE {$before}{$this->table2}{$after} (
    {$before}{$this->IDcol}{$after} INTEGER NOT NULL PRIMARY KEY,
    {$before}{$this->personsidcol}{$after} INTEGER NOT NULL
          CONSTRAINT {$before}fk_addresses_persons_id{$after} REFERENCES {$before}{$this->table1}{$after}({$before}{$this->IDcol}{$after}),
    {$before}{$this->streetcol}{$after} VARCHAR(40) NOT NULL
);

CREATE TABLE {$before}{$this->table3}{$after} (
    {$before}{$this->IDcol}{$after} INTEGER NOT NULL PRIMARY KEY,
    {$before}{$this->personsidcol}${after} INTEGER
          CONSTRAINT {$before}fk_addresses_persons_id{$after} REFERENCES {$before}{$this->table1}{$after}({$before}{$this->IDcol}{$after}) ON DELETE CASCADE,
    {$before}{$this->numbercol}${after} VARCHAR(10) NOT NULL
);
EOT;
        return $sql;
    }

    public function prepareNames($spaces = false) {
        if ($spaces == false) {
            $this->table1 = 'persons';
            $this->table2 = 'addresses';
            $this->table3 = 'phones';
            $this->IDcol = 'id';
            $this->personsidcol = 'persons_id';
            $this->namecol = 'name';
            $this->streetcol = 'street';
            $this->numbercol = 'number';
        } else {
            // only a little contrived
            $this->table1 = 'persons i know';
            $this->table2 = 'personal addresses';
            $this->table3 = 'phone numbers';
            $this->IDcol = 'id number';
            $this->namecol = 'name of person';
            $this->personsidcol = 'persons id';
            $this->streetcol = 'street address';
            $this->numbercol = 'phone number';
        }
    }
}

?>
